#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Datetime: 2020/11/9 7:05
# @Author  : CHEN Wang
# @Site    :
# @File    : fund_holding_related.py
# @Software: PyCharm

"""
脚本说明: 获取基金持仓相关信息
"""
import pandas as pd
from datetime import datetime
from quant_researcher.quant.project_tool import time_tool
from quant_researcher.quant.project_tool.celebrity import get_lst_condition
from quant_researcher.quant.project_tool.db_operator import db_conn, my_mysql
from quant_researcher.quant.project_tool.logger.my_logger import LOG

T_FUND_REPORT_ASSET_ALLOCATION = 'mf_iv_fndassetportfolio'
T_FUND_REPORT_BOND_PORTFOLIO = 'mf_iv_bondportfolio'
T_FUND_REPORT_CONV_BOND_PORTFOLIO = 'mf_iv_convbond_in_swapperiod'
T_FUND_REPORT_STOCK_PORTFOLIO = 'mf_iv_stockportfolio'
T_FUND_REPORT_BOND_ALLOCATION = 'mf_iv_bndvrtalloc'
T_FUND_REPORT_STOCK_ALLOCATION = 'mf_iv_stkindualloc'
T_FUND_ESTIMATED_HOLDING_DAY_HISTORY = 'mf_di_fndholding_estimated_day_history'
T_FUND_ESTIMATED_HOLDING_SEASON = 'mf_di_fndholding_estimated_season'
T_FUND_SHARE_CHG = 'mf_ri_share_chg'
T_FUND_SHARE_CHG_ETF = 'mf_ri_share_chg_ex'
T_FUND_HOLDER_STRUCTURE = 'mf_ri_holderstructure'
T_FUND_FINANCIAL = 'mf_fi_mainindex'
T_STOCK_SHARE_HOLDER = 'jl_base_2010'
EARLIEST_START_DATE = '19000101'


def get_fund_holder_structure(fund_code=None, only_latest=True,
                              start_date=None, end_date=None, select=None):
    """
    获得基金份额持有人结构信息

    :param str or list or None fund_code: 基金代码，支持单个（str）、多个（list）、所有（None）
    :param bool only_latest: 是否只获取最新, 默认为True
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param list select: 需要获得哪些列，默认：None（返回所有列）
    :return: pd.DataFrame
                - holder_total: 持有人户数(户)
                - holdshare_per: 户均持有的基金份额
                - inst_holdshare: 机构投资者持有份额
                - inst_tsppt: 机构投资者持有份额-占总份额比例
                - person_holdshare: 个人投资者持有份额
                - person_tsppt: 个人投资者持有份额-占总份额比例
                - fndmgr_holdshare: 基金管理人持有份额
                - fndmgr_tsppt: 基金管理人持有份额占总份额比例
                - fcstaff_holdshare: 基金公司员工持有份额
                - fcstaff_tsppt: 基金公司员工持有份额占总份额比例
                - fund_holdshare: 基金持有份额
                - fund_tsppt: 基金持有份额占总份额比例
    """
    conn = db_conn.get_basic_data_conn()

    where = []

    # fund_code过滤条件
    if isinstance(fund_code, str) or isinstance(fund_code, list):
        if isinstance(fund_code, str):
            fund_code = [fund_code]
        fund_where_condition = get_lst_condition(fund_code, 'fund_code')
        where.append(fund_where_condition)
    elif fund_code is None:
        fund_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成不带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date, '%Y%m%d')
    if end_date is None:
        end_date = time_tool.get_today(marker='without_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date, '%Y%m%d')

    if only_latest:
        if select is None:
            cols = '*'
        elif isinstance(select, list):
            cols = ', '.join(select)
        else:
            LOG.error(f"select参数为空或为list，请按要求输入")
            return
        sql_str = (f"select {cols} from {T_FUND_HOLDER_STRUCTURE} as a, "
                   f"(select fund_code as code, max(rept_enddate) as enddate "
                   f"from {T_FUND_HOLDER_STRUCTURE} "
                   f"where rept_enddate <= '{end_date}' "
                   f"and rept_type in (5, 6) "
                   f"and {fund_where_condition} "
                   f"group by fund_code) as b "
                   f"where a.fund_code = b.code "
                   f"and a.rept_enddate = b.enddate "
                   f"and a.rept_type in (5, 6)")
        df = my_mysql.read_v3(sql_str, conn)
    else:
        where.append(f"rept_enddate >= '{start_date}'")
        where.append(f"rept_enddate <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=T_FUND_HOLDER_STRUCTURE, conn=conn)

    conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}的数据，请检查")
        return

    if select is None:
        drop_column_list = ['tid', 'titime', 'tutime', 'tutimestamp',
                            'tcreator', 'tmodifier', 'tauditor', 'taudittime', 'tdatastate',
                            'tdatachannel', 'torigdatabuid']
        if only_latest:
            drop_column_list.extend(['code', 'enddate'])
        df.drop(columns=drop_column_list, inplace=True)

    if 'rept_enddate' in df.columns:
        df = df.rename(columns={'rept_enddate': 'report_date'})
        df['report_date'] = df['report_date'].apply(time_tool.format_date_str)
        df.sort_values(by=['report_date', 'fund_code'], ascending=True, inplace=True)  # 按照日期进行排序
    if 'rept_type' in df.columns:
        df = df.rename(columns={'rept_type': 'report_type'})

    return df


def get_fund_share_chg(fund_code, end_date=None, etf=False):
    """
    得到基金的份额变动信息

    :param str or list or None fund_code: 基金代码，支持单个（str）、多个（list）、所有（None）
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param bool etf: 是否是etf基金，若为True，则获取该基金在截止日期的流通份额，
                     否则，获取基金定期公告中发布的基金份额变动信息（etf基金也可以选取etf=False）
    :return: pd.DataFrame
                - begin_share: 期初总份额（份）
                - purchase_share: 期间总申购份额（份）
                - redempt_share: 期间总赎回份额（份）
                - end_share: 期末总份额（份）
                - circul_share: 流通份额（份）
    """
    conn = db_conn.get_basic_data_conn()

    if isinstance(fund_code, str) or isinstance(fund_code, list):
        if isinstance(fund_code, str):
            fund_code = [fund_code]
        fund_where_condition = get_lst_condition(fund_code, 'fund_code')
    elif fund_code is None:
        fund_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    if end_date is None:
        end_date = time_tool.get_today(marker='without_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date, '%Y%m%d')

    if etf:
        df = my_mysql.read_v2(select=['fund_code', 'end_date', 'circul_share'],
                              sfrom=T_FUND_SHARE_CHG_ETF,
                              where=[fund_where_condition, f"end_date = '{end_date}'"],
                              conn=conn)
    else:
        sql_str = (f"select fund_code, rept_enddate as report_date, rept_type as report_type, "
                   f"begin_share, purchase_share, redempt_share, end_share "
                   f"from {T_FUND_SHARE_CHG} as a, "
                   f"(select fund_code as code, max(rept_enddate) as enddate "
                   f"from {T_FUND_SHARE_CHG} "
                   f"where rept_enddate <= '{end_date}' "
                   f"and rept_type in (1, 2, 3, 4, 5, 6) "
                   f"and {fund_where_condition} "
                   f"group by fund_code) as b "
                   f"where a.fund_code = b.code "
                   f"and a.rept_enddate = b.enddate "
                   f"and a.rept_type in (1, 2, 3, 4, 5, 6) ")
        df = my_mysql.read_v3(sql_str, conn)
    conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}的数据，请检查")
        return

    if etf:
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)
    else:
        df['report_date'] = df['report_date'].apply(time_tool.format_date_str)

    return df


def get_bond_type_allocation(fund_code=None, only_latest=True, start_date=None, end_date=None, **kwargs):
    """
    获得基金的债券品种配置信息

    :param str or list or None fund_code: 基金代码，支持单个（str）、多个（list）、所有（None）
    :param bool only_latest: 是否只获取最新, 默认为True
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param kwargs:
        - conn: 数据库连接
    :return: pd.DataFrame
                - fund_code: 基金代码
                - report_date: 报告日期
                - bond_type: 债券类型
                - weight: 债券占比
    """
    conn = kwargs.pop('conn', None)
    select = ['fund_code', 'rept_enddate', 'treasurybond_ppt', 'cbankbills_ppt', 'finbond_ppt',
              'policyfinbond_ppt', 'corpbond_ppt', 'cstfb_ppt', 'mtn_ppt', 'convebond_ppt', 'ncd_ppt']
    df = get_holding_report_info(report='bond_alloc', fund_code=fund_code, only_latest=only_latest,
                                 report_type=[1, 2, 3, 4], start_date=start_date, end_date=end_date, select=select,
                                 conn=conn)
    if df is None:
        LOG.error(f'没有获取到{fund_code}的券种持仓信息，请检查是否有误')
        return
    tmp_df = df.drop(columns=['fund_code', 'report_date'])
    if tmp_df.dropna(how='all').empty:
        return
    bond_type_dict = {'treasurybond_ppt': 'rate_bonds',
                      'cbankbills_ppt': 'rate_bonds',
                      'ex_policy_finbond_ppt': 'credit_bonds',
                      'policyfinbond_ppt': 'rate_bonds',
                      'corpbond_ppt': 'credit_bonds',
                      'cstfb_ppt': 'credit_bonds',
                      'mtn_ppt': 'credit_bonds',
                      'convebond_ppt': 'conver_bonds',
                      'ncd_ppt': 'credit_bonds'}
    df.iloc[:, 2:] /= 100
    df['ex_policy_finbond_ppt'] = df['finbond_ppt'] - df['policyfinbond_ppt']
    df = df.drop(columns=['finbond_ppt'])
    df = df.set_index(['fund_code', 'report_date']).fillna(0).stack().rename('weight').reset_index()
    df['bond_type'] = df['level_2'].replace(bond_type_dict)
    result = df.groupby(['fund_code', 'report_date', 'bond_type'])['weight'].sum().reset_index()

    return result


def get_holding_report_info(report='asset', fund_code=None, only_latest=True, report_type=None,
                            start_date=None, end_date=None, select=None, **kwargs):
    """
    查询一段时间内或最新的基金持仓报告数据，目前支持：
    资产配置
        1 权益投资 eqinvt
            其中：股票 eqsi
                - 存托凭证 （QDII）eqsi_dr
                - 房地产信托 （QDII）eqsi_rte
        2 基金投资 fndinvt
        3 固定收益投资 fixedin
            其中：债券 fibnd
                 资产支持证券 fibnd_abs
        4 贵金属投资 pminvt
        5 金融衍生品投资 fdinvt
            其中: 远期 fdifwd
            其中: 期货 fdifwd_ftrs
            其中: 期权 fdifwd_opts
            其中: 权证 fdifwd_wrts
        6 买入返售金融资产 bbsfa
            其中：买断式回购的买入返售金融资产 bbsfa_borepo
        7 货币市场工具 mmi
        7 银行存款和结算备付金合计 bdlr
        8 其他各项资产 others
        后缀说明：
        _val：绝对值（元）
        _tappt：占总资产比例(%)
        _nappt：占净资产比例(%)
    股票持仓
        持股数量 hold_share
        持股市值 hold_mktvalue（元）
        占净值比例 pptinnv (%)
        占股票比例 pptinstk (%)
        占流通股比例 pptintfstk (%)
    债券持仓
        债券代码 bond_code
        交易所代码 trdmkt_code
        债券简称 bond_sname
        债券类别 bond_type
        持债数量 hold_share
        持债价值 hold_mktvalue（元）
        占净值比例 pptinnv (%)
    可转债券持仓
        债券代码 bond_code
        交易所代码 trdmkt_code
        债券简称 bond_sname
        债券类别 bond_type
        持债数量 hold_share
        持债价值 hold_mktvalue（元）
        占净值比例 pptinnv (%)
    主要会计数据和财务指标
        本期已实现收益 realize_income
        本期利润 profit
        加权平均基金份额本期利润 profit_wa
        本期加权平均净值利润率 profitmargin_wa (%)
        本期基金份额净值增长率 nav_gr (%)
        期末可供分配利润 profit_distributable
        期末可供分配基金份额利润 pps_distributable
        期末基金资产净值 net_asset
        期末基金份额净值 nav
        基金份额累计净值增长率 accunav_gr (%)
    债券品种配置
        国家债券 treasurybond
        央行票据 cbankbills
        金融债券 finbond
            其中：政策性金融债 policyfinbond
        企业债券 corpbond
        企业短期融资券 cstfb
        中期票据 mtn
        可转债（可交换债）convebond
        同业存单 ncd
        其他 others
        合计 total
        剩余存续期超过397天的浮动利率债券 mt397frns
        后缀说明：
        _val：绝对值（元）
        _ppt：占基金资产净值比例(%)
    股票行业配置
        行业分类标准 indu_classstd，证监会行业分类标准2001=csrc2001  证监会行业分类标准2012=csrc2012
        行业代码 indu_code，ZZZ为合计项，非行业代码
        行业名称 indu_name
        市值 mktvalue
        占净资产比例 pptinnv
        投资类型 invest_type，1-综合投资，2-积极投资，3-指数投资

    :param str report: 取哪个报告的数据，
                        asset-资产配置，
                        stock-股票持仓，
                        bond-债券持仓，
                        conv_cond-可转债持仓
                        financial-财务指标，
                        bond_alloc-债券品种配置，
                        stock_alloc-股票行业配置，
                        默认资产配置
    :param str or list or None fund_code: 基金代码，支持单个（str）、多个（list）、所有（None）
    :param bool only_latest: 是否只获取最新, 默认为True
    :param int or list report_type: 报告类型，1-一季报，2-二季报，3-三季报，4-四季报，5-半年报，6-年报，7-其他，默认所有
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param list select: 需要获得哪些列，默认：None（返回所有列）
    :param kwargs:
            - conn: 数据库连接
    :return: pd.DataFrame
    """
    conn = kwargs.pop('conn', None)
    tmp_conn = db_conn.get_basic_data_conn() if conn is None else conn

    if report == 'asset':
        table = T_FUND_REPORT_ASSET_ALLOCATION
    elif report == 'stock':
        table = T_FUND_REPORT_STOCK_PORTFOLIO
    elif report == 'bond':
        table = T_FUND_REPORT_BOND_PORTFOLIO
    elif report == 'conv_bond':
        table = T_FUND_REPORT_CONV_BOND_PORTFOLIO
    elif report == 'financial':
        table = T_FUND_FINANCIAL
    elif report == 'bond_alloc':
        table = T_FUND_REPORT_BOND_ALLOCATION
    elif report == 'stock_alloc':
        table = T_FUND_REPORT_STOCK_ALLOCATION
    else:
        LOG.error(f"目前还不支持{report}该类型的报告")
        return

    if select is not None:
        select = ['rept_enddate' if x == 'report_date' else x for x in select]
        select = ['rept_type' if x == 'report_type' else x for x in select]

    where = []

    # fund_code过滤条件
    if isinstance(fund_code, str) or isinstance(fund_code, list):
        if isinstance(fund_code, str):
            fund_code = [fund_code]
        fund_where_condition = get_lst_condition(fund_code, 'fund_code')
        where.append(fund_where_condition)
    elif fund_code is None:
        fund_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # report_type过滤条件
    if isinstance(report_type, int):
        report_type = [report_type]
    elif report_type is None:
        report_type = [1, 2, 3, 4, 5, 6, 7]
    elif isinstance(report_type, list):
        pass
    else:
        LOG.error('目前report_type只支持int、list或者None，请按要求输入')
        raise NotImplementedError
    report_where_condition = get_lst_condition(report_type, 'rept_type')
    where.append(report_where_condition)
    report_where_condition_4_sql = 'a.' + report_where_condition

    # 将日期转换成不带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date, '%Y%m%d')
    if end_date is None:
        end_date = time_tool.get_today(marker='without_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date, '%Y%m%d')

    if only_latest:
        if select is None:
            cols = '*'
        elif isinstance(select, list):
            cols = ', '.join(select)
        else:
            LOG.error(f"select参数为空或为list，请按要求输入")
            return
        sql_str = (f"select {cols} from {table} as a, "
                   f"(select fund_code as code, max(rept_enddate) as enddate "
                   f"from {table} "
                   f"where rept_enddate <= '{end_date}' "
                   f"and {report_where_condition} "
                   f"and {fund_where_condition} "
                   f"group by fund_code) as b "
                   f"where a.fund_code = b.code "
                   f"and a.rept_enddate = b.enddate "
                   f"and {report_where_condition_4_sql} ")
        df = my_mysql.read_v3(sql_str, tmp_conn)
    else:
        where.append(f"rept_enddate >= '{start_date}'")
        where.append(f"rept_enddate <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=tmp_conn)

    if conn is None:
        tmp_conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}的数据，请检查")
        return

    if select is None:
        drop_column_list = ['tid', 'titime', 'tutime', 'tutimestamp',
                            'tcreator', 'tmodifier', 'tauditor', 'taudittime', 'tdatastate',
                            'tdatachannel', 'torigdatabuid']
        if only_latest:
            drop_column_list.extend(['code', 'enddate'])
        df.drop(columns=drop_column_list, inplace=True)

    if 'rept_enddate' in df.columns:
        df = df.rename(columns={'rept_enddate': 'report_date'})
        df['report_date'] = df['report_date'].apply(time_tool.format_date_str)
        df.sort_values(by=['report_date', 'fund_code'], ascending=True, inplace=True)  # 按照日期进行排序
    if 'rept_type' in df.columns:
        df = df.rename(columns={'rept_type': 'report_type'})

    return df


def get_estimated_holding_info(report='day', fund_code=None, only_latest=True,
                               start_date=None, end_date=None, select=None, with_future_data=False):
    """
    查询一段时间内或最新的基金持仓穿透报告数据，目前支持：

    模拟持仓
        基金代码 fund_code
        预测日期 estimated_date
        基金一级分类 fund_type_1
        基金二级分类 fund_type_2
        资产类型 asset_type
        资产代码 asset_code
        持仓占净资产比率 estimated_weight
        持仓市值 value
        持仓价格 price
        持仓份额 num_shares

    :param str report: 取哪个报告的数据，day-每天的持仓穿透数据，season-每个季度的持仓穿透数据，默认每天的持仓穿透数据
    :param str or list or None fund_code: 基金代码，支持单个（str）、多个（list）、所有（None）
    :param bool only_latest: 是否只获取最新, 默认为True
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param list select: 需要获得哪些列，默认：None（返回所有列）
    :param bool with_future_data: 是否包含未来数据，默认False-不包含，只针对report='day'时生效
    :return: pd.DataFrame
    """
    # 表目前在测试环境，等表同步到生产环境之后conn改为生产环境的数据库连接
    conn = db_conn.get_derivative_data_write_conn()

    if report == 'day':
        table = T_FUND_ESTIMATED_HOLDING_DAY_HISTORY
    elif report == 'season':
        table = T_FUND_ESTIMATED_HOLDING_SEASON
    else:
        LOG.error(f"目前还不支持{report}该类型的报告")
        return

    where = []

    if isinstance(select, list) and 'special_sign' not in select and report == 'day':
        select.append('special_sign')

    # fund_code过滤条件
    if isinstance(fund_code, str) or isinstance(fund_code, list):
        if isinstance(fund_code, str):
            fund_code = [fund_code]
        fund_where_condition = get_lst_condition(fund_code, 'fund_code')
        where.append(fund_where_condition)
    elif fund_code is None:
        fund_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = time_tool.format_date_str(EARLIEST_START_DATE)
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)

    if only_latest:
        if select is None:
            cols = '*'
        elif isinstance(select, list):
            cols = ', '.join(select)
        else:
            LOG.error(f"select参数为空或为list，请按要求输入")
            return
        sql_str = (f"select {cols} from {table} as a, "
                   f"(select fund_code as code, max(estimated_date) as enddate "
                   f"from {table} "
                   f"where estimated_date <= '{end_date}' "
                   f"and {fund_where_condition} "
                   f"group by fund_code) as b "
                   f"where a.fund_code = b.code "
                   f"and a.estimated_date = b.enddate ")
        df = my_mysql.read_v3(sql_str, conn)
    else:
        where.append(f"estimated_date >= '{start_date}'")
        where.append(f"estimated_date <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=conn)

    conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}的数据，请检查")
        return

    if report == 'day':
        if not with_future_data:
            df = df[df['special_sign'] == 0]
        else:
            df_count = df.groupby(['fund_code', 'estimated_date'])['special_sign'].nunique()
            df = df.set_index(['fund_code', 'estimated_date'])
            df_1 = df[df['special_sign'] == 1]
            if not df_1.empty:
                index_0 = df_count[df_count == 1].index
                df_0 = df.loc[index_0]
                df = pd.concat([df_0, df_1], sort=False).reset_index()
            else:
                df = df.reset_index()

    if select is None:
        drop_column_list = ['tid', 'titime', 'tutime', 'tutimestamp']
        if only_latest:
            drop_column_list.extend(['code', 'enddate'])
        df.drop(columns=drop_column_list, inplace=True)
    if isinstance(select, list) and 'special_sign' not in select and report == 'day':
        df.drop(columns=['special_sign'], inplace=True)

    if 'estimated_date' in df.columns:
        df['estimated_date'] = df['estimated_date'].apply(time_tool.format_date_str)

    return df


def get_holding_from_share_holder_info(fund_code, only_latest=True,
                                       start_date=None, end_date=None):
    """
    通过股票的股东名单信息找到基金定期报告披露持仓以外的其他股票持仓信息

    :param str or list fund_code: 基金代码，支持单个（str）、多个（list）
    :param bool only_latest: 是否只获取最新, 默认为True
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :return:
    """
    conn = db_conn.get_third_party_conn()

    select = ['stockcode as stock_code', 'F2010_032 as num_shares',
              'F2010_024 as fund_code', 'ReportDate as report_date']

    where = []

    # fund_code过滤条件
    if isinstance(fund_code, str):
        fund_code = [fund_code]
    elif isinstance(fund_code, list):
        pass
    else:
        LOG.error('目前fund_code只支持str、list，请按要求输入')
        raise NotImplementedError
    fund_where_condition = get_lst_condition(fund_code, 'F2010_024')
    where.append(fund_where_condition)

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date, '%Y%m%d')
    if end_date is None:
        end_date = time_tool.get_today(marker='without_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date, '%Y%m%d')

    start_date = datetime.strptime(start_date, '%Y%m%d')
    end_date = datetime.strptime(end_date, '%Y%m%d')

    if only_latest:
        cols = ', '.join(select)
        sql_str = (f"select {cols} from {T_STOCK_SHARE_HOLDER} as a, "
                   f"(select F2010_024 as f_code, max(ReportDate) as enddate "
                   f"from {T_STOCK_SHARE_HOLDER} "
                   f"where ReportDate <= '{end_date}' "
                   f"and {fund_where_condition} "
                   f"and F2010_012 = 2 "
                   f"and F2010_016 in ('开放式投资基金', '封闭式投资基金') "
                   f"group by F2010_024) as b "
                   f"where a.F2010_024 = b.f_code "
                   f"and a.ReportDate = b.enddate "
                   f"and a.F2010_012 = 2 "
                   f"and a.F2010_016 in ('开放式投资基金', '封闭式投资基金')")
        df = my_mysql.read_v3(sql_str, conn)
    else:
        where.append(f"ReportDate >= '{start_date}'")
        where.append(f"ReportDate <= '{end_date}'")
        where.append('F2010_012 = 2')  # 2表示十大流通股股东
        where.append("F2010_016 in ('开放式投资基金', '封闭式投资基金')")
        df = my_mysql.read_v2(select=select, where=where, sfrom=T_STOCK_SHARE_HOLDER, conn=conn)

    conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}的数据，请检查")
        return

    df['report_date'] = df['report_date'].apply(time_tool.format_date_str)

    return df


"""
之前的API函数，目前都替换成最新的API函数，以下函数已弃用
"""

# def get_holding_report_info(report='asset', fund_list=None, only_latest=False,
#                             quarterly_or_annual='quarterly',
#                             start_date=None, end_date=None, select=None):
#     """
#     查询一段时间内或最新的基金持仓报告数据，目前支持：
#     资产配置
#         1 权益投资 eqinvt
#             其中：股票 eqsi
#                 - 存托凭证 （QDII）eqsi_dr
#                 - 房地产信托 （QDII）eqsi_rte
#         2 基金投资 fndinvt
#         3 固定收益投资 fixedin
#             其中：债券 fibnd
#                  资产支持证券 fibnd_abs
#         4 贵金属投资 pminvt
#         5 金融衍生品投资 fdinvt
#             其中: 远期 fdifwd
#             其中: 期货 fdifwd_ftrs
#             其中: 期权 fdifwd_opts
#             其中: 权证 fdifwd_wrts
#         6 买入返售金融资产 bbsfa
#             其中：买断式回购的买入返售金融资产 bbsfa_borepo
#         7 货币市场工具 mmi
#         7 银行存款和结算备付金合计 bdlr
#         8 其他各项资产 others
#     股票持仓
#         持股数量 hold_share
#         持股市值 hold_mktvalue
#         占净值比例 pptinnv
#         占股票比例 pptinstk
#         占流通股比例 pptintfstk
#     债券持仓
#         债券类别 bond_type
#         持债数量 hold_share
#         持债价值 hold_mktvalue
#         占净值比例 pptinnv
#     主要会计数据和财务指标
#         本期已实现收益 realize_income
#         本期利润 profit
#         加权平均基金份额本期利润 profit_wa
#         本期加权平均净值利润率 profitmargin_wa
#         本期基金份额净值增长率 nav_gr
#         期末可供分配利润 profit_distributable
#         期末可供分配基金份额利润 pps_distributable
#         期末基金资产净值 net_asset
#         期末基金份额净值 nav
#         基金份额累计净值增长率 accunav_gr
#
#     :param str report: 取哪个报告的数据，asset-资产配置，stock-股票持仓，bond-债券持仓，financial-财务指标，默认资产配置
#     :param list fund_list: 基金代码列表, 如['001410', '003095'], 默认为None，即获取所有基金
#     :param bool only_latest: 是否只获取最新, 默认为False
#     :param str quarterly_or_annual: quarterly-季报，annual-年报，both-季报年报都输出, 默认为季报
#     :param str start_date: 开始时间 "2020-01-01" （开始时间不传则默认为end_date）
#     :param str end_date: 结束时间 "2020-09-01" (结束时间不传则默认为今天)
#     :param list select: 需要获得哪些列，默认：None（返回所有列）
#     :return: pd.DataFrame
#
#     是否经过测试：
#     """
#
#     conn = db_conn.get_basic_data_conn()
#
#     if report == 'asset':
#         table = T_FUND_REPORT_ASSET_ALLOCATION
#     elif report == 'stock':
#         table = T_FUND_REPORT_STOCK_ALLOCATION
#     elif report == 'bond':
#         table = T_FUND_REPORT_BOND_ALLOCATION
#     elif report == 'financial':
#         table = T_FUND_FINANCIAL
#     else:
#         LOG.error(f"目前还不支持{report}该类型的报告")
#         return
#
#     if end_date is None:
#         end_date = time_tool.get_today(marker='with_n_dash')
#     end_date = end_date.replace('-', '')
#     if not only_latest:
#         if start_date is None:
#             start_date = end_date
#             LOG.error(f"获取一段时间内的资产配置变化，需要指定start_date, 未指定则start_date=end_date")
#         else:
#             start_date = start_date.replace('-', '')
#
#     if select is None:
#         cols = '*'
#     elif isinstance(select, list):
#         cols = ', '.join(select)
#     else:
#         LOG.error(f"select参数为空或为list，请按要求输入")
#         return
#
#     if fund_list is None:
#         fund_list = fund_info.get_all_fund_code()
#     fund_where_condition = get_lst_condition(fund_list, 'fund_code')
#
#     if quarterly_or_annual == 'quarterly':
#         report_type = '(1, 2, 3, 4)'
#     elif quarterly_or_annual == 'annual':
#         report_type = '(5, 6)'
#     elif quarterly_or_annual == 'both':
#         report_type = '(1, 2, 3, 4, 5, 6)'
#     else:
#         LOG.error(f"quarterly_or_annual参数只支持quarterly、annual、both，请按要求输入")
#         return
#
#     if only_latest:
#         df = pd.read_sql(f"select {cols} from {table} as a, "
#                          f"(select fund_code as code, max(rept_enddate) as enddate "
#                          f"from {table} "
#                          f"where rept_enddate <= '{end_date}' "
#                          f"and rept_type in {report_type} "
#                          f"and {fund_where_condition} "
#                          f"group by fund_code) as b "
#                          f"where a.fund_code = b.code "
#                          f"and a.rept_enddate = b.enddate "
#                          f"and a.rept_type in {report_type} ", conn)
#         if select is None:
#             df.drop(columns=['code', 'enddate', 'tid', 'titime', 'tutime', 'tutimestamp',
#                              'tcreator', 'tmodifier', 'tauditor', 'taudittime', 'tdatastate',
#                              'tdatachannel', 'torigdatabuid'], inplace=True)
#     else:
#         df = pd.read_sql(f"select {cols} from {table} "
#                          f"where {fund_where_condition} "
#                          f"and rept_enddate >= '{start_date}' and rept_enddate <= '{end_date}' "
#                          f"and rept_type in {report_type} ", conn)
#     conn.close()
#
#     return df


# def get_estimated_holding_info(report='day', fund_code=None, only_latest=True, start_date=None,
#                                end_date=None, select=None, conn=None):
#     """
#     查询一段时间内或最新的基金持仓穿透报告数据，目前支持：
#
#     模拟持仓
#         资产类型
#         资产代码
#         持仓占净资产比率
#         持仓市值
#         持仓价格
#         持仓份额
#
#     :param str report: 取哪个报告的数据，day-每天的持仓穿透数据，season-每个季度的持仓穿透数据，默认每天的持仓穿透数据
#     :param list fund_code: 基金代码列表, 如['001410', '003095'], 默认为None，即获取所有基金
#     :param bool only_latest: 是否只获取最新, 默认为True
#     :param str start_date: 开始时间 "2020-01-01" （开始时间不传则默认为end_date）
#     :param str end_date: 结束时间 "2020-09-01" (结束时间不传则默认为不包括今天的最近一个交易日)
#     :param list select: 需要获得哪些列，默认：None（返回所有列）
#     :param conn: 数据库连接
#     :return: pd.DataFrame
#
#     是否经过测试：
#     """
#     if conn is None:
#         conn = db_conn.get_derivative_data_write_conn(by_sqlalchemy=True)
#
#     if report == 'day':
#         table = T_FUND_ESTIMATED_HOLDING_DAY
#     elif report == 'season':
#         table = T_FUND_ESTIMATED_HOLDING_SEASON
#     else:
#         LOG.error(f"目前还不支持{report}该类型的报告")
#         return
#
#     where_condition = ''
#     if end_date is None:
#         end_date = time_tool.get_today(marker='with_n_dash')
#         end_date = t_trade_date.date_shifter_by_trade_date(end_date, how_many=1,
#                                                            before_or_after='before')
#     end_date = end_date.replace('-', '')
#     if only_latest:
#         where_condition += f"estimated_date = '{end_date}' "
#     else:
#         if start_date is not None:
#             start_date = start_date.replace('-', '')
#             where_condition += (f"estimated_date >= '{start_date}' and estimated_date <= "
#                                 f"'{end_date}' ")
#         else:
#             where_condition += f"estimated_date = '{end_date}' "
#
#     if fund_code is not None:
#         where_condition = where_condition + ' and ' + (get_lst_condition(fund_code, 'fund_code'))
#
#     if select is None:
#         cols = '*'
#     elif isinstance(select, list):
#         cols = ', '.join(select)
#     else:
#         LOG.error(f"select参数为空或为list，请按要求输入")
#         return
#
#     df = pd.read_sql(f"select {cols} from {table} where {where_condition} ", conn)
#
#     return df
#

# def get_fund_bond_type_allocation(fund_list=None, quarterly_or_annual='quarterly',
#                                   only_latest=True, start_date=None, end_date=None):
#     if start_date is not None:
#         start_date= start_date.replace('-','')
#     end_date= end_date.replace('-','')
#     bond_type_dict = {'treasurybond_ppt': 'rate_bonds',
#                       'cbankbills_ppt': 'rate_bonds',
#                       'ex_policy_finbond_ppt': 'credit_bonds',
#                       'policyfinbond_ppt':'rate_bonds',
#                       'corpbond_ppt': 'credit_bonds',
#                       'cstfb_ppt': 'credit_bonds',
#                       'mtn_ppt': 'credit_bonds',
#                       'convebond_ppt': 'conver_bonds',
#                       'ncd_ppt': 'credit_bonds'}
#
#     conn = db_conn.get_basic_data_conn()
#     if quarterly_or_annual == 'quarterly':
#         report_type = '(1, 2, 3, 4)'
#     elif quarterly_or_annual == 'annual':
#         report_type = '(5, 6)'
#     elif quarterly_or_annual == 'both':
#         report_type = '(1, 2, 3, 4, 5, 6)'
#     else:
#         LOG.error(f"quarterly_or_annual参数只支持quarterly、annual、both，请按要求输入")
#         return
#     cols = "fund_code, rept_enddate, treasurybond_ppt, cbankbills_ppt, finbond_ppt, " \
#            "policyfinbond_ppt, corpbond_ppt, cstfb_ppt, mtn_ppt, convebond_ppt, " \
#            "ncd_ppt"
#     if only_latest:
#         df = pd.read_sql(f"select {cols} from mf_iv_bndvrtalloc as a, "
#                          f"(select fund_code as code, max(rept_enddate) as enddate "
#                          f"from mf_iv_bndvrtalloc "
#                          f"where rept_enddate <= '{end_date}' "
#                          f"and rept_type in {report_type} "
#                          f"and fund_code in {tuple(fund_list + [''])} "
#                          f"group by fund_code) as b "
#                          f"where a.fund_code = b.code "
#                          f"and a.rept_enddate = b.enddate "
#                          f"and a.rept_type in {report_type} ", conn)
#     else:
#         df = pd.read_sql(f"select {cols} from mf_iv_bndvrtalloc "
#                          f"where fund_code in {tuple(fund_list + [''])} "
#                          f"and rept_type in {report_type} "
#                          f"and rept_enddate >= '{start_date}' "
#                          f"and rept_enddate <= '{end_date}' ", conn)
#     df.iloc[:, 2:] /= 100
#     df['ex_policy_finbond_ppt'] = df['finbond_ppt'] - df['policyfinbond_ppt']
#     df = df.drop(columns=['finbond_ppt'])
#     df = df.set_index(['fund_code','rept_enddate']).stack().rename('weight').reset_index()
#     df['bond_type'] = df['level_2'].replace(bond_type_dict)
#     result = df.groupby(['rept_enddate','bond_type'])['weight'].sum().reset_index()
#     conn.close()
#     if result.empty:
#         LOG.info('没有获取到该基金的券种持仓信息，请检查是否有误')
#         result = pd.DataFrame({'bond_type': ['rate_bonds', 'credit_bonds', 'conver_bonds']})
#         result['rept_enddate'] = end_date
#         result['weight'] = 0
#     return result


if __name__ == '__main__':
    # aaa = get_holding_report_info('asset', ['110022', '003095'], False, [1, 2, 3, 4], '2018-01-01', '2020-01-01')
    # bbb = get_fund_holder_structure('110022', False, '2019-01-01', '2020-01-01')
    ccc = get_estimated_holding_info(report='day', fund_code=['001410', '110022'], only_latest=False,
                                     start_date='2020-09-01', end_date='2020-11-30', with_future_data=True)
    # ddd = get_fund_share_chg(['110022', '003095'], '2020-12-01', False)
    # eee = get_holding_from_share_holder_info(['110022', '003095'], False, '20200630', '20200930')
